--
-- num_nulls()
--
SELECT
    num_nonnulls (NULL);

SELECT
    num_nonnulls ('1');

SELECT
    num_nonnulls (NULL::text);

SELECT
    num_nonnulls (NULL::text, NULL::int);

SELECT
    num_nonnulls (1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);

SELECT
    num_nonnulls (VARIADIC '{1,2,NULL,3}'::int[]);

SELECT
    num_nonnulls (VARIADIC '{"1","2","3","4"}'::text[]);

SELECT
    num_nonnulls (VARIADIC ARRAY (
            SELECT
                CASE WHEN i <> 40 THEN
                    i
                END
            FROM generate_series(1, 100) i));

SELECT
    num_nulls (NULL);

SELECT
    num_nulls ('1');

SELECT
    num_nulls (NULL::text);

SELECT
    num_nulls (NULL::text, NULL::int);

SELECT
    num_nulls (1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);

SELECT
    num_nulls (VARIADIC '{1,2,NULL,3}'::int[]);

SELECT
    num_nulls (VARIADIC '{"1","2","3","4"}'::text[]);

SELECT
    num_nulls (VARIADIC ARRAY (
            SELECT
                CASE WHEN i <> 40 THEN
                    i
                END
            FROM generate_series(1, 100) i));

-- special cases
SELECT
    num_nonnulls (VARIADIC NULL::text[]);

SELECT
    num_nonnulls (VARIADIC '{}'::int[]);

SELECT
    num_nulls (VARIADIC NULL::text[]);

SELECT
    num_nulls (VARIADIC '{}'::int[]);

-- should fail, one or more arguments is required
SELECT
    num_nonnulls ();

SELECT
    num_nulls ();

--
-- Test adding a support function to a subject function
--
CREATE FUNCTION my_int_eq (int, int)
    RETURNS bool
    LANGUAGE internal
    STRICT IMMUTABLE PARALLEL SAFE
    AS $$
    int4eq
$$;

-- By default, planner does not think that's selective
EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    tenk1 a
    JOIN tenk1 b ON a.unique1 = b.unique1
WHERE
    my_int_eq (a.unique2, 42);

-- With support function that knows it's int4eq, we get a different plan
ALTER FUNCTION my_int_eq (int, int) SUPPORT test_support_func;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    tenk1 a
    JOIN tenk1 b ON a.unique1 = b.unique1
WHERE
    my_int_eq (a.unique2, 42);

-- Also test non-default rowcount estimate
CREATE FUNCTION my_gen_series (int, int)
    RETURNS SETOF integer
    LANGUAGE internal
    STRICT IMMUTABLE PARALLEL SAFE
    AS $$
    generate_series_int4
$$ SUPPORT test_support_func;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    tenk1 a
    JOIN my_gen_series (1, 1000) g ON a.unique1 = g;

EXPLAIN (
    COSTS OFF
)
SELECT
    *
FROM
    tenk1 a
    JOIN my_gen_series (1, 10) g ON a.unique1 = g;

